/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IDetailDemandeDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Article;
import com.nxlv2.nomad.businessobjects.DetailDemande;
import com.nxlv2.nomad.businessobjects.Prix;
import com.nxlv2.nomad.businessobjects.TypeDemande;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcDetailDemandeDao implements IDetailDemandeDao{
    private static final Logger logger = LoggerFactory.getLogger(JdbcDetailDemandeDao.class);
    private static final ConnectionManager manager = new ConnectionManager();
    /**
     * 
     * @param detailDemande
     * @throws DAOException 
     */
    @Override
    public void saveDetailDemande(DetailDemande detailDemande) throws DAOException {
        
        if (null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();
        StringBuilder builder = new StringBuilder();
        builder.append("INSERT INTO detaildemande (idBase, quantite, dateCreation, idAppliTypeDemande ) "
                + "VALUES (?, ?, NOW(), ?)");
        String request = builder.toString();
        logger.debug("Insert detailDemande: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, detailDemande.getIdBase());
            ps.setInt(2, detailDemande.getQuantite());
            ps.setInt(3, detailDemande.getTypeDemande().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            detailDemande.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }
    /**
     * 
     * @param id
     * @throws DAOException 
     */
    @Override
    public void deleteDetailDemande(int id) throws DAOException {        
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM detaildemande WHERE idAppli = ?");
        String request = builder.toString();
        logger.debug("Delete detailDemande: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete article with id.");
            throw new DAOException("Can not delete article with id.", e);
        }
        manager.fermerConnection();
    }
    /**
     * 
     * @param detailDemande
     * @throws DAOException 
     */
    @Override
    public void updateDetailDemande(DetailDemande detailDemande) throws DAOException {
        if (null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        Connection con = manager.creerConnection();

        StringBuilder builder = new StringBuilder();
        // {{{ COR [type_demande]
        builder.append("UPDATE detaildemande ");
        builder.append("SET idBase = ?, ");
        builder.append("quantite = ?, ");
        builder.append("dateModification = NOW() ");
        builder.append("WHERE idAppli = ? ");
         builder.append("AND idAppliTypeDemande = ? ");
        String request = builder.toString();
        logger.debug("Update detaildemande: {}", request);

        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, detailDemande.getIdBase());
            ps.setInt(2, detailDemande.getQuantite());
            ps.setInt(3, detailDemande.getIdAppli()); 
            ps.setInt(4, detailDemande.getTypeDemande().getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        // COR }}}
        manager.fermerConnection();
    }
    
    /**
     * 
     * @param id
     * @return DetailDemande
     * @throws DAOException 
     */
    @Override
    public List<DetailDemande> findDetailDemandeById(int id) throws DAOException{        
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli , idBase, quantite, idAppliTypeDemande ");
        builder.append("FROM detaildemande WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("get detaildemande: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeQuery();
        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<DetailDemande> detailDemandes = null;
        try {
            detailDemandes = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.");
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == detailDemandes || detailDemandes.isEmpty()) {
            throw new NoResultException("No detailDemande found with id " + id);
        }
        if (detailDemandes.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return detailDemandes;
    }
    /**
     * 
     * @param rst
     * @return List<DetailDemande>
     * @throws SQLException 
     */
    private List<DetailDemande> map(ResultSet rst) throws SQLException {
        List<DetailDemande> detailDemandes = new ArrayList<>();

        while (rst.next()) {
            DetailDemande detailDemande = new DetailDemande();
            detailDemande.setIdAppli(rst.getInt("idAppli"));
            detailDemande.setIdBase(rst.getInt("idBase"));
            detailDemande.setQuantite(rst.getInt("quantite"));
            detailDemande.setTypeDemande(TypeDemande.getByID(rst.getInt("idAppliTypeDEmande")));
            detailDemandes.add(detailDemande);
        }
        return detailDemandes;
    }
    
    /**
     * 
     * @param detailDemande
     * @return Article
     * @throws DAOException 
     */
    @Override
    public Article getArticle(DetailDemande detailDemande) throws DAOException {
        if ( null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT article.idBase, article.idAppli, ");
        builder.append("AES_DECRYPT(article.libelle,?) AS libelle, ");
        builder.append("AES_DECRYPT(article.reference,?) AS reference ");       
        builder.append("FROM detailDemande dd ");
        builder.append("JOIN article ON article.idAppli = dd.idAppliArticle ");
        builder.append("WHERE dd.idAppli = ?");

        String request = builder.toString();
        logger.debug("Select article par detailDemande: {}", request);
        logger.debug("detailDemande {}", detailDemande);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());            
            ps.setInt(3, detailDemande.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Article> articles = null;
        try {
            articles = mapArticle(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == articles || articles.isEmpty()) {
            throw new NoResultException("No article found with detailDemande : " + detailDemande);
        }
manager.fermerConnection();
        return articles.get(0);
    }

    /**
     * 
     * @param article
     * @param detailDemande
     * @throws DAOException 
     */
    @Override
    public void lierArticleADetailDemande(Article article, DetailDemande detailDemande) throws DAOException {
        if (null == article || null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE detailDemande ");
        builder.append("SET idAppliArticle = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier article à detailDemande : {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, article.getIdAppli());
            ps.setInt(2, detailDemande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link article to detailDemande.");
            throw new DAOException("Can not link article to detailDemande.", e);
        }
        manager.fermerConnection();
    }
    
    /**
     * 
     * @param rst
     * @return List<Article>
     * @throws SQLException 
     */
    private List<Article> mapArticle(ResultSet rst) throws SQLException {
        List<Article> articles = new ArrayList<>();

        while (rst.next()) {
            Article article = new Article();
            article.setIdAppli(rst.getInt("idAppli"));
            article.setIdBase(rst.getInt("idBase"));
            article.setLibelle(rst.getString("libelle"));
            article.setReference(rst.getString("reference"));
            articles.add(article);
        }
        return articles;
    }
    
    /**
     * 
     * @param detailDemande
     * @return Prix
     * @throws DAOException 
     */
    @Override
    public Prix getPrix(DetailDemande detailDemande) throws DAOException {
        if ( null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }
        StringBuilder builder = new StringBuilder();
        builder.append("SELECT prix.idBase, prix.idAppli, ");
        builder.append("prix.prixUht, prix.date ");            
        builder.append("FROM detailDemande dd ");
        builder.append("JOIN prix ON prix.idAppli = dd.idAppliPrix ");
        builder.append("WHERE dd.idAppli = ?");

        String request = builder.toString();
        logger.debug("Select prix par detailDemande: {}", request);
        logger.debug("detailDemande {}", detailDemande);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {           
            ps.setInt(1, detailDemande.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Prix> prixs = null;
        try {
            prixs = mapPrix(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == prixs || prixs.isEmpty()) {
            throw new NoResultException("No article found with detailDemande : " + detailDemande);
        }
manager.fermerConnection();
        return prixs.get(0);
    }

    /**
     * 
     * @param prix
     * @param detailDemande
     * @throws DAOException 
     */
    @Override
    public void lierPrixADetailDemande(Prix prix, DetailDemande detailDemande) throws DAOException {
        if (null == prix || null == detailDemande) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE detailDemande ");
        builder.append("SET idAppliPrix = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier prix à detailDemande : {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, prix.getIdAppli());
            ps.setInt(2, detailDemande.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link prix to detailDemande.");
            throw new DAOException("Can not link prix to detailDemande.", e);
        }
        manager.fermerConnection();
    }
    
    /**
     * 
     * @param rst
     * @return List<Prix>
     * @throws SQLException 
     */
    private List<Prix> mapPrix(ResultSet rst) throws SQLException {
        List<Prix> prixs = new ArrayList<>();

        while (rst.next()) {
            Prix prix = new Prix();
            prix.setIdAppli(rst.getInt("idAppli"));
            prix.setIdBase(rst.getInt("idBase"));
            prix.setPrixUht(rst.getDouble("prixUht"));
            prix.setDate(rst.getDate("date"));
            prixs.add(prix);
        }
        return prixs;
    }
}
